package ext.csc.component.numbergen.loader;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import ext.csc.component.numbergen.classification.Classification;
import ext.csc.component.numbergen.classification.ClassificationHelper;
import ext.csc.component.numbergen.classification.ClassificationInfoContained;
import ext.csc.component.numbergen.connection.DBConn;


public class ClassificationLoader {
	private DataSource data;
	private ClassificationTranslated translator;
	private LoadReporter reporter;
	
	public ClassificationLoader(DataSource data, ClassificationTranslated translator){
		this.data = data;
		this.translator = translator;
		reporter = new LoadReporter();
	}

	public void load(){
		loadClassification();
	}

	private void loadClassification(){
		String class_path = "" ;
		String class_parent = "";
		String class_name = "";
		String class_value = "";
		String class_desc = "";
		
		DBConn conn = null;
		try {
			conn = new DBConn();
			
			for (int i = translator.getRowBegin(); i <= data.end(); i++) {
				HashMap record = data.getRecordData(i);
				translator.translate(record);
				ClassificationInfoContained classInfo = translator.getClassInfo();
			
				class_path = "'" + classInfo.getObjectclasspath() + "' ";
				class_parent = "'" + classInfo.getObjectparentclasspath() + "' ";
				class_name = "'" + classInfo.getObjectname() + "' ";
				class_value = "'" + classInfo.getObjectclassvalue() + "' ";
				class_desc = "'" + classInfo.getObjectclassdesc() + "' ";
				

				String sql;
				sql = "SELECT * FROM csc_numberclassification WHERE " + 
						Classification.CLASSIFICATION_PATH + " = " + class_path + 
						" AND " + Classification.CLASSIFICATION_PARENTPATH + " = " + class_parent;
				ResultSet rs = conn.executeQuery(sql);
				
				if(!rs.next()){
					sql = "INSERT INTO csc_numberclassification " + 
								"(" + Classification.CLASSIFICATION_PATH + "," + Classification.CLASSIFICATION_PARENTPATH + "," + 
								Classification.CLASSIFICATION_NAME + "," + Classification.CLASSIFICATION_VALUE + "," + 
								Classification.CLASSIFICATION_DESC + ") " +
								"VALUES(" + class_path + "," + class_parent + "," + class_name + "," + class_value + "," + class_desc + ")";
				}else{
					sql = "UPDATE csc_numberclassification SET " + 
							Classification.CLASSIFICATION_PARENTPATH + " = " + class_parent + ", " + 
							Classification.CLASSIFICATION_NAME + " = " + class_name + ", " + 
							Classification.CLASSIFICATION_VALUE + " = " + class_value +  ", " + 
							Classification.CLASSIFICATION_DESC + " = " + class_desc + 
							" WHERE " + 
							Classification.CLASSIFICATION_PATH + " = " + class_path;
				}

				conn.executeUpdate(sql);
				System.out.println("Load line " + i + " sucessful!");
			}
			
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			System.out.println("*** New Classification Terminated!! ***  Class path: " + class_path + " Failed!");
		}finally{
			try {
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	public LoadReporter getReporter() {
		return reporter;
	}
}
